<?php
class CiuusersController extends AppController {
	var $name = 'Ciuusers';
	var $components = array(
        'Auth'=>array(
        'userModel' => 'Ciuuser',
        'loginError' => "Usuario/Clave equivocada",
        'loginRedirect' => array('controller' => 'ciuusers', 'action' => 'home'),
        'autoRedirect' => false, /* evita la redireccion inmediata y le deja el control al metodo local */
        'loginAction' => array(
                  'controller' => 'ciuusers',
                  'action' => 'login',
                  'plugin' => false,
                 'admin' => false,
		)
		)
		, 'Session'
	);

    var $uses = array('Ciuuser','Ciuparameter','Ciucourse','CiucourseCiustudent','Ciusubject','Ciuacademicperiod','Ciucoursestate','Ciulanguage','Ciustudenttype');

	public function beforeFilter() {
		parent::beforeFilter();
		$this->Auth->allow('resendactivationmail','activate','register','login','home','sendmail','forgot','reset','sendmailparam');
	}
	
	function isAuthorized() {
        $result=false;
		if ( in_array(  array('register'),    $this->action) ) {
            $result=true;
        }
        return $result;
    }
	
	function login(){
		$result=false;
		$this->layout="login";
		$username=$this->data["Ciuuser"]["username"];	
		if ($this->Ciuuser->hasAny(array(
		 'Ciuuser.username' => $username,
		 'Ciuuser.verified' => 1 ))) {
			$greeting="Bienvenido, $username";
			$this->Session->setFlash(__($greeting, true));
			//$this->redirect(array('action' => 'home'));
			$this->Auth->userModel = 'Ciuuser';	 
			$this->Auth->login( $this->data );
			if( $this->Auth->_loggedIn){
				$this->redirect(array('controller'=>'ciuusers', 'action' => 'home'));
			}
		}else{
			if($this->Ciuuser->hasAny(array('Ciuuser.username'=>$username))){
				$this->Session->setFlash(__('User needs to be verified', true));
			}else{
				$this->Session->setFlash(__('Usuario no registrado en el sistema. Favor registrarse.', true));
			}
			$this->Auth->_loggedIn=false;
		}	
		return $result;
	}

	function logout() {
		$goodbye="Hasta pronto!";
		$this->Session->setFlash(__($goodbye, true));
		$this->redirect($this->Auth->logout());
	}

	function sendmail($data=null){
		if(empty($data)){
			$inner=array('email'=>'johana.rios@unillanos.edu.co','first_names'=>'Alfonso','activation_key'=>'8485d613ede59172ef0c7a6bf5ae60b7','username'=>'987');
			$data=array('Ciuuser'=>$inner);
		}
		$this->Email->from = "Centro de Idiomas Unillanos <institutodeidiomas@unillanos.edu.co>";//Configure::read('Site.title') . ' '. '<admin@' . preg_replace('#^www\.#', '', strtolower($_SERVER['SERVER_NAME'])).'>';
		$this->Email->to = $data['Ciuuser']['email'];
		$this->Email->subject = sprintf(__('[%s]  Please activate your account', true), Configure::read('Site.title'));
		$this->Email->template = 'register';
		$this->set('user', $data);
		$this->Email->delivery = 'smtp';
		$this->Email->send();
		$this->set('smtp_errors', $this->Email->smtpError);
		$this->Session->setFlash(__('You have successfully registered an account. An email has been sent with further instructions.', true), 'default', array('class' => 'success'));
	}

	function sendmailparam($data=null){
		if(empty($data)){
			$inner=array('email'=>'johana.rios@unillanos.edu.co','first_names'=>'Alfonso','activation_key'=>'8485d613ede59172ef0c7a6bf5ae60b7','username'=>'987');
			$data=array('Ciuuser'=>$inner);
		}
		$this->Email->from = "Idiomas Unillanos <institutodeidiomas@unillanos.edu.co>";Configure::read('Site.title') . ' '. '<admin@' . preg_replace('#^www\.#', '', strtolower($_SERVER['SERVER_NAME'])).'>';
		$this->Email->to = $data['Ciuuser']['email'];
		$this->Email->subject = sprintf(__('[%s]  Please activate your account', true), Configure::read('Site.title'));
		$this->Email->template = 'register';
		$this->set('user', $data);
		$this->Email->send();
		$this->set('smtp_errors', $this->Email->smtpError);
		$this->Session->setFlash(__('You have successfully registered an account. An email has been sent with further instructions.', true), 'default', array('class' => 'success'));
		$this->redirect(array('action' => 'login'));
	}

    //activa un usuario por medio de una llave enviada en un correo.
	public function activate($username = null, $key = null) {
		$smtp_errors="";
		if ($username != null && $key != null) {
			if ($this->Ciuuser->hasAny(array(
				'Ciuuser.username' => $username,
				'Ciuuser.activation_key' => $key,
				'Ciuuser.verified' => 0,
			))) {
				$user = $this->Ciuuser->findByUsername($username);
				$this->Ciuuser->id = $user['Ciuuser']['id'];
				$this->Ciuuser->saveField('verified', 1);  // queda verificado.
                                $this->Ciuuser->savefield('ciuuserstate_id',1);//queda activo
				$this->Ciuuser->saveField('activation_key', md5(uniqid()));
				$this->Session->setFlash(__('Cuenta de usuario activada correctamente. Favor ingresar al sistema.', true), 'default', array('class' => 'success'));
			
			} else {
				$this->Session->setFlash(__('Usuario ya activado o no coincide el numero de activación.', true), 'default', array('class' => 'error'));
				$smtp_errors="Usuario ya activado o no coincide el numero de activación.";
			}
		}
		$this->redirect(array('action' => 'login'));
	}

	function register() {
		$this->layout="clean";
		$data=array();
		if(!empty($this->data)) {
			$this->Ciuuser->create();
			$data=$this->data['Ciuuser'];
			$data['username'] = $data['document'];
			$data['name'] = $data['first_names'];
			$data['activation_key']= md5(uniqid());				
			$data['isactive']=0;  //se activa cuando oprime el link en el correo q se envia.
			$data['verified']=0;  
			$data['ciuusertype_id']=1;  //estudiante
			$data['ciuadmisionstate_id']=1;  //entramite		
			if($data['document'] >0  ){
				$cantidad_existente = $this->Ciuuser->find('count', array('conditions' => array('Ciuuser.document' => $data['document'])));
				if($cantidad_existente < 1 ){
					if($this->Ciuuser->save($data)) {
						$this->sendmail(array('Ciuuser'=>$data));
						$this->redirect(array('action' => 'login'));
					}
				}else{
					$this->Session->setFlash(__('Document already registered', true));
				}
			}else{
				$this->Session->setFlash(__('Document must be numeric', true));
			}
		}
		$sql="Select distinct plan.id
			  from ciuplans plan
			  join ciusubjects subject on  plan.id=  subject.ciuplan_id
			  where plan.id <>5";  // que no traiga el plan "Ninguno"
		 
		$plansubjectraw=$this->Ciuuser->query($sql);
		$plan=array();
		foreach($plansubjectraw as $key => $row){
			$plan[]=$row["plan"]["id"];
		 }                        
		$ciutimetables = $this->Ciuuser->Ciutimetable->find('list');
		$ciuplans = $this->Ciuuser->Ciuplan->find('list',array('conditions'=>array('Ciuplan.id'=>$plan)));
		$this->set('ciutimetables',$ciutimetables);
		$this->set('ciuplans',$ciuplans);
	}

	function home(){

	}

	function index() {
		$this->Ciuuser->recursive = 0;
		$this->set('ciuusers', $this->paginate());
	}

	function view($id = null) {
		if (!$id) {
			$this->Session->setFlash(__('Invalid ciuuser', true));
			$this->redirect(array('action' => 'index'));
		}
		$this->set('ciuuser', $this->Ciuuser->read(null, $id));
	}

	function add() {
		if (!empty($this->data)) {
			$this->Ciuuser->create();
			if ($this->Ciuuser->save($this->data)) {
				$this->Session->setFlash(__('The ciuuser has been saved', true));
				$this->redirect(array('action' => 'index'));
			} else {
				$this->Session->setFlash(__('The ciuuser could not be saved. Please, try again.', true));
			}
		}
		$ciuuserstates = $this->Ciuuser->Ciuuserstate->find('list');
		$ciuusertypes = $this->Ciuuser->Ciuusertype->find('list');
		$ciustudenttypes = $this->Ciuuser->Ciustudenttype->find('list');
		$this->set(compact('ciuuserstates', 'ciuusertypes', 'ciustudenttypes'));
	}

	function edit($id = null) {
		if (!$id && empty($this->data)) {
			$this->Session->setFlash(__('Invalid ciuuser', true));
			$this->redirect(array('action' => 'index'));
		}
		if (!empty($this->data)) {
			if ($this->Ciuuser->save($this->data)) {
				$this->Session->setFlash(__('The ciuuser has been saved', true));
				$this->redirect(array('action' => 'index'));
			} else {
				$this->Session->setFlash(__('The ciuuser could not be saved. Please, try again.', true));
			}
		}
		if (empty($this->data)) {
			$this->data = $this->Ciuuser->read(null, $id);
		}
		$ciuuserstates = $this->Ciuuser->Ciuuserstate->find('list');
		$ciuusertypes = $this->Ciuuser->Ciuusertype->find('list');
		$ciustudenttypes = $this->Ciuuser->Ciustudenttype->find('list');
		$this->set(compact('ciuuserstates', 'ciuusertypes', 'ciustudenttypes'));
	}

	function delete($id = null) {
		if (!$id) {
			$this->Session->setFlash(__('Invalid id for ciuuser', true));
			$this->redirect(array('action'=>'index'));
		}
		if ($this->Ciuuser->delete($id)) {
			$this->Session->setFlash(__('Ciuuser deleted', true));
			$this->redirect(array('action'=>'index'));
		}
		$this->Session->setFlash(__('Ciuuser was not deleted', true));
		$this->redirect(array('action' => 'index'));
	}

	function indexapplicant( $filter_id = 1){
		$controller='Ciuuser';
		$key = "ciuusertype_id";
		$varInView="ciuusers";
		$conditions=array();
		$conditions[$controller . "." . $key]= $filter_id;
		$conditions[$controller . ".ciuadmisionstate_id" ]=1;
                $conditions[$controller . ".verified" ]=1;
		if (!empty($this->data)) {
			$document= $this->data['Ciuuser']['document'];
			$conditions[$controller . ".document" ]=$document;
		}
		$this->paginate[$controller]['conditions'] = $conditions;
		$this->set($varInView, $this->paginate());
	}
	
	function indexstudent( $filter_id = 1){
		$controller='Ciuuser';
		$key = "ciuusertype_id";
		$varInView="ciuusers";
		$conditions=array();
		$conditions[$controller . "." . $key]= $filter_id;
		if (!empty($this->data)) {
			$document= $this->data['Ciuuser']['document'];
			$conditions[$controller . ".document" ]=$document;
		}
		$this->paginate[$controller]['conditions'] = $conditions;
		$this->set($varInView, $this->paginate());
	}

	function editstudent($id = null) {
        $target="indexstudent";
		if (!$id && empty($this->data)) {
			$this->Session->setFlash(__('Invalid ciuuser', true));
			$this->redirect(array('action' => $target));
		}
		$data=array();
		if (!empty($this->data)) {
			$this->Ciuuser->create();
			$data=$this->data['Ciuuser'];
			if($data['document'] >0){
                            $data['username']=$data['document'];
				$esDuplicado = $this->Ciuuser->isDuplicated($this->data['Ciuuser']);
				if($esDuplicado){
					$this->Session->setFlash(__('Document already registered', true));
				}else {
					if ($this->Ciuuser->save($data)) {
						$this->Session->setFlash(__('The teacher has been saved', true));
						$this->redirect(array('action' => $target));
					}else {
						$this->Session->setFlash(__('The teacher could not be saved. Please, try again.', true));
					}
				}
			}else {
				$this->Session->setFlash(__('Document must be numeric', true));
			}
		}
		if (empty($this->data)) {
			$this->data = $this->Ciuuser->read(null, $id);
		}
		$ciuuserstates = $this->Ciuuser->Ciuuserstate->find('list');
		$ciuusertypes = $this->Ciuuser->Ciuusertype->find('list');
		$ciustudenttypes = $this->Ciuuser->Ciustudenttype->find('list');
		$this->set(compact('ciuuserstates', 'ciuusertypes', 'ciustudenttypes'));
	}

	function viewstudent($id = null) {
		if (!$id) {
			$this->Session->setFlash(__('Invalid ciuuser', true));
			$this->redirect(array('action' => 'indexstudent'));
		}
		$this->set('ciuuser', $this->Ciuuser->read(null, $id));
	}

    function indexteacher( $filter_id = 2){
        $controller='Ciuuser';
        $key = "ciuusertype_id";
        $varInView="ciuusers";
        $conditions=array();
        $conditions[$controller . "." . $key]= $filter_id;
        if (!empty($this->data)) {
            $document= $this->data['Ciuuser']['document'];
            $conditions[$controller . ".document" ]=$document;
        }    
        $this->paginate[$controller]['conditions'] = $conditions;   
        $this->set($varInView, $this->paginate());
    }

	function editteacher($id = null) {
		$target="indexteacher";
		if (!$id && empty($this->data)) {
			$this->Session->setFlash(__('Invalid ciuuser', true));
			$this->redirect(array('action' => $target));
		}
		$data=array();
		if (!empty($this->data)) {
			$this->Ciuuser->create();
			$data=$this->data['Ciuuser'];
			if($data['document'] >0){
                                $data['username']=$data['document'];
				$esDuplicado = $this->Ciuuser->isDuplicated($this->data['Ciuuser']);
				if($esDuplicado){
					$this->Session->setFlash(__('Document already registered', true));
				}else {
					if ($this->Ciuuser->save($data)) {
						$this->Session->setFlash(__('The teacher has been saved', true));
						$this->redirect(array('action' => $target));
					}else {
						$this->Session->setFlash(__('The teacher could not be saved. Please, try again.', true));
					}
				}
			}else {
				$this->Session->setFlash(__('Document must be numeric', true));
			}
		}
		if (empty($this->data)) {
			$this->data = $this->Ciuuser->read(null, $id);
		}
		$ciuuserstates = $this->Ciuuser->Ciuuserstate->find('list');
		$ciuusertypes = $this->Ciuuser->Ciuusertype->find('list');
		$ciustudenttypes = $this->Ciuuser->Ciustudenttype->find('list');
		$this->set(compact('ciuuserstates', 'ciuusertypes', 'ciustudenttypes'));
	}

	function viewteacher($id = null) {
		$target="indexteacher";
		if (!$id) {
			$this->Session->setFlash(__('Invalid ciuuser', true));
			$this->redirect(array('action' => $target));
		}
		$this->set('ciuuser', $this->Ciuuser->read(null, $id));
	}

	function checkregistration($id=null){
		$this->layout="";
		$result="Documento OK";
		if(isset($id) && $id && $id>0){
			$arr = $this->Ciuuser->findByDocument($id);
			if(!empty($arr)){
				$result="$id : Documento YA registrado";
			}
		}else{
			$result="Documento No Válido";
		}
		$this->set('result', $result );
	}

	function editchangepass($id = null) {
		if (!$id && empty($this->data)) {
			$this->Session->setFlash(__('Invalid ciuuser', true));
			$this->redirect(array('action' => 'indexchangepass'));
		}
		if (!empty($this->data)) {
			if ($this->Ciuuser->save($this->data)) {
				$this->Session->setFlash(__('The ciuuser has been saved', true));
				$this->redirect(array('action' => 'indexchangepass'));
			} else {
				$this->Session->setFlash(__('The ciuuser could not be saved. Please, try again.', true));
			}
		}
		if (empty($this->data)) {
			$this->data = $this->Ciuuser->read(null, $id);
		}
		$ciuuserstates = $this->Ciuuser->Ciuuserstate->find('list');
		$ciuusertypes = $this->Ciuuser->Ciuusertype->find('list');
		$ciustudenttypes = $this->Ciuuser->Ciustudenttype->find('list');
		$this->set(compact('ciuuserstates', 'ciuusertypes', 'ciustudenttypes'));
	}
	
	function indexchangepass() {
		if (!empty($this->data)) {
			$document= $this->data['Ciuuser']['document'];
			$conditions[$controller . ".document" ]=$document;
		}
		$this->paginate[$controller]['conditions'] = $conditions;   
		$this->set($varInView, $this->paginate());
	}
	
	public function forgot() {
		$model="Ciuuser";
		$this->set('title_for_layout', __('Forgot Password', true));
		if (!empty($this->data) && isset($this->data[$model]['username'])) {
			$user = $this->Ciuuser->findByUsername($this->data[$model]['username']);
			if (!isset($user[$model]['id'])) {
				$this->Session->setFlash(__('Invalid username.', true), 'default', array('class' => 'error'));
				$this->redirect(array('action' => 'login'));
			}
			$this->Ciuuser->id = $user[$model]['id'];
			$activationKey = md5(uniqid());
			$this->Ciuuser->saveField('activation_key', $activationKey);
			$this->set(compact('user', 'activationKey'));
			$this->Email->from ="alfonsoayalapaloma@gmail.com";
			$this->Email->to = $user[$model]['email'];
			$this->Email->subject = sprintf(__('[%s] Reset Password', true), Configure::read('Site.title'));
			$this->Email->template = 'forgot_password';
			$res=$this->Email->send();
			if ($res) {
				$this->Session->setFlash(__('An email has been sent with instructions for resetting your password.', true), 'default', array('class' => 'success'));
			} else {
				$this->Session->setFlash(__('An error occurred. Please try again.', true), 'default', array('class' => 'error'));
			}
			$this->set('smtp_errors', $this->Email->smtpError);
		}
		$this->set('smtp_errors', $this->Email->smtpError);
	}

	public function reset($username = null, $key = null) {
		$this->set('title_for_layout', __('Reset Password', true));
		if (  !empty($this->data)){
			$username=$this->data['Ciuuser']['username'];
			$key=$this->data['Ciuuser']['key'];
		}
		if (  empty($this->data) &&(  $username == null || $key == null)  ) {
			$this->Session->setFlash(__('An error occurred.', true), 'default', array('class' => 'error'));
			$this->redirect(array('action' => 'login'));
		}
		$user = $this->Ciuuser->find('first', array(
			'conditions' => array(
				'Ciuuser.username' => $username,
				'Ciuuser.activation_key' => $key,
		),
		));
		if (!isset($user['Ciuuser']['id'])) {
			$this->Session->setFlash(__('User not found.', true), 'default', array('class' => 'error'));
			$this->redirect(array('action' => 'login'));
		}
		if (!empty($this->data) && isset($this->data['Ciuuser']['password'])) {
			$this->Ciuuser->id = $user['Ciuuser']['id'];
			$user['Ciuuser']['password'] = Security::hash($this->data['Ciuuser']['password'], null, true);
			$user['Ciuuser']['activation_key'] = md5(uniqid());
			$options = array('fieldList' => array('password', 'activation_key'));
			if ($this->Ciuuser->save($user['Ciuuser'], $options)) {
				$this->Session->setFlash(__('Your password has been reset successfully.', true), 'default', array('class' => 'success'));
				$this->redirect(array('action' => 'login'));
			} else {
				$this->Session->setFlash(__('An error occurred. Please try again.', true), 'default', array('class' => 'error'));
			}
		}
		$this->set(compact('user', 'username', 'key'));
	}

	function changepass(){
		App::import('Component', 'Session');
		$mySession = new SessionComponent();
		$userInfo = $mySession->read('Auth.Ciuuser');
		$username=$userInfo['username'];
		$firstnames=$userInfo['first_names'];
		$ciuuserData = $this->Ciuuser->findByUsername($username);
		$ciuuser_id=$ciuuserData['Ciuuser']['id'];
		if(!empty($this->data)){
			$id=$ciuuser_id;
			$username=$this->data['Ciuuser']['username'];
			$key=$this->data['Ciuuser']['password'];
			$pass1=$this->data['Ciuuser']['newpassword'];
			$pass2=$this->data['Ciuuser']['newpasswordagain'];
			if($pass1==$pass2){
				if ($this->Ciuuser->hasAny(array(
					'Ciuuser.username' => $username,
					'Ciuuser.password' => $key,
				))) {
					$user=array();
					$user['id']=$id;
					$user['password']=Security::hash($this->data['Ciuuser']['newpassword'], null, true);
					$user['verified']=1;
					$this->Ciuuser->save(array('Ciuuser'=>$user));
					$this->Session->setFlash(__('Password changed.', true), 'default', array('class' => 'success'));
					$this->redirect(array('action' => 'login'));
				}else{
					$this->Session->setFlash(__('Wrong password.', true), 'default', array('class' => 'success'));
				}
			}else{
				$this->Session->setFlash(__('Passwords dont match.', true), 'default', array('class' => 'success'));		
			}
		}
		$this->set('username',$username);
		$this->set('id',$ciuuser_id);
	}

    function addteacher() {
		$data=array();
		if(!empty($this->data)) {
			$this->Ciuuser->create();
			$data=$this->data['Ciuuser'];
			$data['username'] = $data['document']; // usuario de ingreso
			$data['name'] = $data['first_names'];  // nombre 
			$data['ciuusertype_id']=2; //Docente
			$data['ciuuserstate_id']=1; // activo
			$data['isactive']=1; //  activo
			$data['verified']=1; //  activo
			$data['ciustudenttype_id']=6; //No-estudiante
			if($data['document'] >0){
			   $rels = $this->Ciuuser->find('count', array('conditions' => array('Ciuuser.document' => $data['document'])));
			   if($rels < 1 ){
					if($this->Ciuuser->save($data)) {
						$this->Session->setFlash(__('The ciuuser has been saved', true));
						$this->redirect(array('action' => 'indexteacher'));
					}else {
						$this->Session->setFlash(__('The ciuuser could not be saved. Please, try again.', true));
					}
				}else {
					$this->Session->setFlash(__('Document already registered', true));
				}
			}else {
				$this->Session->setFlash(__('Document must be numeric', true));
			}
		}
	}

	// listado de aspirantes inscritos por plan
 	function demand($lenguage_id=1){
		$queryDemanda=
                        "select Ciuplan.id,Ciuplan.name, Ciutimetable.id, Ciutimetable.name, count(*) as demanda
                         from ciuusers Ciuuser
                         join ciuplans Ciuplan on (Ciuplan.id=Ciuuser.ciuplan_id)
                         join ciutimetables Ciutimetable on (Ciutimetable.id=Ciuuser.ciutimetable_id) 
                         where ciuadmisionstate_id=1
                         and verified=1
                         and ciuuserstate_id=1
                         group by Ciuplan.id,Ciuplan.name, Ciutimetable.id, Ciutimetable.name";
		$demand= $this->Ciuuser->query($queryDemanda);
		$this->set('demand',$demand);
	}

	//admite un estudiante
	function admit($id=null,$exam_id=null){
		if (!$id ) {
			$this->Session->setFlash(__('Invalid ciuuser', true));
		}
		if ($id) {
			//actualiza el estudiante
            $datosEstudiante=array();
			$datosEstudiante['Ciuuser']['id']=$id;
			$datosEstudiante['Ciuuser']['ciuadmisionstate_id']=3;  // admitido
			if ($this->Ciuuser->save($datosEstudiante)) {
				$this->Session->setFlash(__('El aspirante fue admitido.', true));
				$this->redirect(array('controller'=>'ciuexams', 'action' => 'view', $exam_id));
			} else {
				$this->Session->setFlash(__('No se pudo actualizar el registro. Favor reintentar.', true));
			}
		}
		if (empty($this->data)) {
			$this->data = $this->Ciuuser->read(null, $id);
		}
		$this->redirect(array('controller'=>'ciuexams', 'action' => 'view', $exam_id));
	}

	function reject($id=null,$exam_id=null){
		if (!$id ) {
			$this->Session->setFlash(__('Invalid ciuuser', true));
		}
		if ($id) {
            //actualiza estudiante
			$datosEstudiante=array();
			$datosEstudiante['Ciuuser']['id']=$id;
			$datosEstudiante['Ciuuser']['ciuadmisionstate_id']=4;  //NO admitido
			if ($this->Ciuuser->save($datosEstudiante)) {
				$this->Session->setFlash(__('El aspirante fue rechazado.', true));
				$this->redirect(array('controller'=>'ciuexams', 'action' => 'view', $exam_id));
			} else {
				$this->Session->setFlash(__('The ciuuser could not be saved. Please, try again.', true));
			}
		}
		if (empty($this->data)) {
			$this->data = $this->Ciuuser->read(null, $id);
		}
		$this->redirect(array('controller'=>'ciuexams', 'action' => 'view', $exam_id));
	}
	 
    //tabla resumen de los usuarios admitidos.   
	function showadmitted( ){
		$sql="select Ciutimetable.id, Ciutimetable.name, Ciuuser.ciuplan_id, Ciuplan.name, Ciusubject.id , Ciusubject.name, count(*) as qty ".
		" from ciuusers Ciuuser ".
		" join ciutimetables Ciutimetable on (Ciuuser.ciutimetable_id=Ciutimetable.id) ".
		" join ciuexam_ciuusers CiuexamCiuuser on (Ciuuser.id=CiuexamCiuuser.ciuuser_id) ".
		" join ciuexams Ciuexam on (Ciuexam.id=CiuexamCiuuser.ciuexam_id) ". 
		" join ciuplans Ciuplan on (Ciuexam.ciuplan_id=Ciuplan.id) ". 
        " join ciusubjects Ciusubject on (CiuexamCiuuser.ciusubject_id=Ciusubject.id)".
		" where Ciuuser.ciuadmisionstate_id=3 ".
		" and Ciuexam.ciuacademicperiod_id in ( select id from ciuacademicperiods where isactive=1)  "  .
        " and Ciuuser.verified=1".
        " and Ciuuser.ciuuserstate_id=1".
        " group by Ciutimetable.id, Ciutimetable.name, Ciuuser.ciuplan_id, Ciuplan.name, Ciusubject.id, Ciusubject.name  ";
		$admitted= $this->Ciuuser->query($sql);
		$this->set('demand',$admitted);
	}
	
	function viewinfo() {
		App::import('Component', 'Session');
		$mySession = new SessionComponent();
		$userInfo = $mySession->read('Auth.Ciuuser');
		$username=$userInfo['username'];
		$id=$userInfo['id'];
		$firstnames=$userInfo['first_names'];
		if (!$id) {
			$this->Session->setFlash(__('Invalid ciuuser', true));
			$this->redirect(array('action' => 'home'));
		}
		$this->set('ciuuser', $this->Ciuuser->read(null, $id));
    }
	
    function viewpdf($tipopdf = null , $id = null , $language_id = null) { 
        if (!$id) 
        { 
            $this->Session->setFlash('Sorry, there was no property ID submitted.'); 
        } 
        Configure::write('debug',0); // Otherwise we cannot use this method while developing 
        if ($tipopdf=='constancia'){
            $this->constancia($id,$language_id); // here the data is pulled from the database and set for the view 
        }
        elseif ($tipopdf=='certificado') {
            $this->certificado($id,$language_id);
        }    
        $this->layout = 'pdf'; //this will use the pdf.ctp layout 
        $this->render(); 
    }
    
    function constanciaform(){
        $estudianteId=$this->Ciuuser->findByDocument($this->data['Ciuuser']['document']);
        $estudianteId=$estudianteId['Ciuuser']['id'];
        $languageId=$this->data['Ciuuser']['ciulanguage_id'];
        if(!empty($this->data)){
           $this->redirect(array('controller' => 'ciuusers', 'action' => 'viewpdf','constancia',$estudianteId,$languageId));
        }
        $ciulanguages =$this->Ciulanguage->find('list');
        $this->set(compact('ciulanguages'));
    }
    
    function certificadoform(){
        $estudianteId=$this->Ciuuser->findByDocument($this->data['Ciuuser']['document']);
        $estudianteId=$estudianteId['Ciuuser']['id'];
        $languageId=$this->data['Ciuuser']['ciulanguage_id'];       
        if(!empty($this->data)){
            $this->redirect(array('controller' => 'ciuusers', 'action' => 'viewpdf','certificado',$estudianteId,$languageId));
        }
        $ciulanguages =$this->Ciulanguage->find('list');
        $this->set(compact('ciulanguages'));
    }
    
    function constancia($id = null , $language_id = null ){

        $periodo         =$this->Ciucourse->Ciuacademicperiod->getCurrent();
        $infoPeriodo     =$this->Ciuacademicperiod->findById($periodo);
        $nombrePeriodo   =$infoPeriodo['Ciuacademicperiod']['name'];
        $year            =substr($infoPeriodo['Ciuacademicperiod']['start_date'],0,4);
        $curso_estudiante=$this->CiucourseCiustudent->find('first', array('conditions' => 
						array('CiucourseCiustudent.ciuuser_id'          => $id,          //nos lo envian
							  'CiucourseCiustudent.ciucurspersstate_id' => 3,            //debe tener matriculada la materia
							  'Ciusubject.ciulanguage_id'               => $language_id, //subject tiene el language
							  'Ciucourse.ciuacademicperiod_id'          => $periodo      //debe ser el periodo q se encuentra activo
							  )
						 )
		  );
        
        if(empty($curso_estudiante)){
            $this->Session->setFlash(__('No es posible generar la constancia con los datos ingresados', true));
            $this->redirect(array('action' => 'constanciaform'));  
        }

        $infoIdioma=$this->Ciulanguage->findById($language_id);
        $lenguaje  =$infoIdioma['Ciulanguage']['name'];

        $dias = array("Domingo","Lunes","Martes","Miercoles","Jueves","Viernes","Sábado");
        $meses = array("Enero","Febrero","Marzo","Abril","Mayo","Junio","Julio","Agosto","Septiembre","Octubre","Noviembre","Diciembre");
        
        $params   =$this->Ciuparameter->findByName('CUERPO_CONSTANCIAS');
        $paramsFin=sprintf(    $params['Ciuparameter']['definition'],
                /*nombre    */ $curso_estudiante['Ciuuser']['first_names']." ".$curso_estudiante['Ciuuser']['last_names'],
                /*num_docu  */ $curso_estudiante['Ciuuser']['document'],
         /*ciudad_expedicion*/ $curso_estudiante['Ciuuser']['expedition_city'],
                /*nivel     */ $curso_estudiante['Ciusubject']['level'],
                /*lenguaje  */ $lenguaje,
                /*periodo   */ $nombrePeriodo,
                /*año       */ $year,
                /*presencial*/ $curso_estudiante['Ciusubject']['presencial'],
                /*extra     */ $curso_estudiante['Ciusubject']['extra'],
		/*fecha certificado*/  $dias[date('w')]." ".date('d')." de ".$meses[date('n')-1]. " del ".date('Y')."." 
                );
        $this->set('nomdoc',"Constancia_".$curso_estudiante['Ciuuser']['document'].".pdf");
        $this->set('titulodoc',"HACE CONSTAR");
        $this->set('params',$paramsFin);
   }
   
   function certificado($id = null , $language_id = null ){
        
        $periodo         =$this->Ciucourse->Ciuacademicperiod->getCurrent();
        $curso_estudiante=$this->CiucourseCiustudent->find('first', array('conditions' => 
                                                                        array('CiucourseCiustudent.ciuuser_id'          => $id,          //nos lo envian
                                                                              'CiucourseCiustudent.ciucurspersstate_id' => 4,            //debe tener matriculada la materia
                                                                              'Ciusubject.ciulanguage_id'               => $language_id, //subject tiene el language
                                                                              'Ciucourse.ciuacademicperiod_id <>'       => $periodo      //debe ser el periodo q se encuentra activo
                                                                              )
                                                                         )
                                                          );
        
        $infoPeriodo     =$this->Ciuacademicperiod->findById($curso_estudiante['Ciucourse']['ciuacademicperiod_id']);
        $nombrePeriodo   =$infoPeriodo['Ciuacademicperiod']['name'];
        $year            =substr($infoPeriodo['Ciuacademicperiod']['start_date'],0,4);
         
        $infoIdioma=$this->Ciulanguage->findById($language_id);
        $lenguaje  =$infoIdioma['Ciuplan']['name'];
        
        $dias = array("Domingo","Lunes","Martes","Miercoles","Jueves","Viernes","Sábado");
        $meses = array("Enero","Febrero","Marzo","Abril","Mayo","Junio","Julio","Agosto","Septiembre","Octubre","Noviembre","Diciembre");
        
        if(empty($curso_estudiante)){
            $this->Session->setFlash(__('No es posible generar el certificado con los datos ingresados', true));
            $this->redirect(array('action' => 'certificadoform'));  
        }
      
        $params   =$this->Ciuparameter->findByName('CUERPO_CERTIFICACIONES');
        $paramsFin=sprintf(    $params['Ciuparameter']['definition'],
                /*nombre    */ $curso_estudiante['Ciuuser']['first_names']." ".$curso_estudiante['Ciuuser']['last_names'],
                /*num_docu  */ $curso_estudiante['Ciuuser']['document'],
                /*ciudad_expedicion*/ $curso_estudiante['Ciuuser']['expedition_city'],
                /*nivel     */ $curso_estudiante['Ciusubject']['level'],
                /*lenguaje  */ $lenguaje,
                /*periodo   */ $nombrePeriodo,
                /*año       */ $year,
                /*presencial*/ $curso_estudiante['Ciusubject']['presencial'],
                /*extra     */ $curso_estudiante['Ciusubject']['extra'],
         /*fecha certificado*/ $dias[date('w')]." ".date('d')." de ".$meses[date('n')-1]. " del ".date('Y')."."
                );
        $this->set('nomdoc',"Certificado_".$curso_estudiante['Ciuuser']['document'].".pdf");
        $this->set('titulodoc',"CERTIFICA");
        $this->set('params',$paramsFin);
   }
   
   function consulta_general($period_id = null, $language_id = null, $studenttype_id=null){        
        $curso_estudiante=$this->CiucourseCiustudent->find('all', array('conditions' => 
                array('Ciusubject.ciulanguage_id'      => $language_id, //subject tiene el language
                          'Ciucourse.ciuacademicperiod_id' => $period_id,
                          'Ciustudenttype.id'=>$studenttype_id
                          )
                 )
        );
        $sep=";";
        $infoPeriodo     =$this->Ciuacademicperiod->findById($period_id);
        $nombrePeriodo   =$infoPeriodo['Ciuacademicperiod']['name'];
        $year            =substr($infoPeriodo['Ciuacademicperiod']['start_date'],0,4);
        $infoIdioma=$this->Ciusubject->findById($language_id);
        $lenguaje  =$infoIdioma['Ciuplan']['name'];
        $reporte=array();  
        
        $reporte[]="Numero de Documento".$sep.
                   "Tipo de Documento".$sep.
                   "Nombres".$sep.
                   "Apellidos".$sep.
                   "Correo Electronico".$sep.
                   "Nivel Academico".$sep.
                   "Tipo estudiante".$sep.
                   "Descripción".$sep.
                   "Idioma nivel".$sep.
                   "Estado".$sep.
                   "Nota Final".$sep.
                   "Periodo Academico";
        foreach($curso_estudiante as $key => $val){
            
           $id_estado=$val['CiucourseCiustudent']['ciucurspersstate_id'];
           $estado=$this->Ciucoursestate->findById($id_estado);
           $estado=$estado['Ciucoursestate']['name'];
           $id_tipo=$val['CiucourseCiustudent']['ciustudenttype_id'];
           $tipo=$this->Ciustudenttype->findById($id_tipo);
           $tipo=$tipo['Ciustudenttype']['name'];
               
           $line=/*num documento*/$val['Ciuuser']['document'].$sep.
                 /*tipo document*/$val['Ciuuser']['document_type'].$sep.
                 /*nombres      */$val['Ciuuser']['first_names'].$sep.
                 /*apellidos    */$val['Ciuuser']['last_names'].$sep.
                 /*correo electr*/$val['Ciuuser']['email'].$sep.
                 /*nivel academi*/$val['Ciuuser']['academic_level'].$sep.
                 /*Tipo estudiante*/$tipo.$sep.
                 /*Descripción  */$val['CiucourseCiustudent']['descristudenttype'].$sep.
                 /*nivel        */$val['Ciusubject']['name'].$sep.
                 /*estado matric*/$estado.$sep.
                 /*nota final   */$val['CiucourseCiustudent']['total_score'].$sep.
                 /*periodo acade*/$nombrePeriodo
                 ;
           $reporte[]=$line;
        }
        return $reporte;
   }
   
	function consulta_generalcsv(){
       $period_id=$this->Session->read("sessionPeriod_id");
       $language_id=$this->Session->read("sessionLanguage_id");
       $studenttype_id=$this->Session->read("sessionStudenttype_id");
       $params=$this->consulta_general($period_id,$language_id,$studenttype_id);
       $this->set('filename',"Reporte_General_" . date("Y-m-d") . ".csv");
       $this->layout="";
       $this->set('params',$params);
	}
   
	function consulta_generalhtml(){   
       $period_id=$this->data['Ciuuser']['ciuacademicperiod_id'];
       $language_id=$this->data['Ciuuser']['ciulanguage_id'];
       $studenttype_id=$this->data['Ciuuser']['ciustudenttype_id'];
       $params=array();
       if(!empty($this->data)){
            $params=$this->consulta_general($period_id,$language_id,$studenttype_id);
       }
       $this->set('params',$params);
       
       $ciulanguages =$this->Ciulanguage->find('list');
       $ciuacademicperiods =$this->Ciuacademicperiod->find('list');
       $ciustudenttypes= $this->Ciustudenttype->find('list',array('conditions'=>array('Ciustudenttype.id !='=>6) ));
       $this->set(compact('ciuacademicperiods','ciulanguages','ciustudenttypes'));
       
       $this->Session->write("sessionPeriod_id", $period_id);
       $this->Session->write("sessionLanguage_id", $language_id);
       $this->Session->write("sessionStudenttype_id", $studenttype_id);
	}
   
	function editinfo() {
		App::import('Component', 'Session');
		$mySession = new SessionComponent();
		$userInfo = $mySession->read('Auth.Ciuuser');
		$username=$userInfo['username'];
		$id=$userInfo['id'];
		if (!$id && empty($this->data)) {
		  $this->Session->setFlash(__('Invalid ciuuser', true));
		  $this->redirect(array('action' => 'viewinfo'));
		}
		if (!empty($this->data)) {
		  if ($this->Ciuuser->save($this->data)) {
			$this->Session->setFlash(__('The ciuuser has been saved', true));
			$this->redirect(array('action' => 'viewinfo'));
		  } else {
			$this->Session->setFlash(__('The ciuuser could not be saved. Please, try again.', true));
		  }
		}
		if (empty($this->data)) {
		  $this->data = $this->Ciuuser->read(null, $id);
		}
		$ciuuserstates = $this->Ciuuser->Ciuuserstate->find('list');
		$ciuusertypes = $this->Ciuuser->Ciuusertype->find('list');
		$ciustudenttypes = $this->Ciuuser->Ciustudenttype->find('list');
		$this->set(compact('ciuuserstates', 'ciuusertypes', 'ciustudenttypes'));
	}
 
	function studentlistform(){
		if (!empty($this->data)){
		   $cursoId=$this->data['Ciuuser']['Curso'];
		   $this->redirect(array('controller' => 'ciuusers', 'action' => 'studentlistpdf',$cursoId));
		}
		//listado de cursos con estudiantes
		$sqlCourses="select ciucourse_ciustudent.ciucourse_id, count(ciucourse_ciustudent.ciuuser_id) totalStudents
							from ciucourse_ciustudents ciucourse_ciustudent 
							group by ciucourse_ciustudent.ciucourse_id
							having count(ciucourse_ciustudent.ciuuser_id) > 0";
		
		$listCourses= $this->Ciuuser->query($sqlCourses);		
		$courses=array();
		foreach ($listCourses as $row){
			$courses[]=$row['ciucourse_ciustudent']['ciucourse_id'];
		}
		//cursos disponibles con estudiantes matricualdos, el resultado se mostrará en la lista desplegable
		$cursosDisponibles=$this->CiucourseCiustudent->Ciucourse->find('all',
							array(
							'fields'=>array('Ciucourse.id', 'concat(Ciusubject.name," - ",Ciuroom.name," - ",Ciutimetable.name) as name'),
							'conditions'=>array('Ciuacademicperiod.isactive'=>1,
												'Ciucourse.id'=>$courses
											   )
								)
							);
		$opciones=array();
		foreach($cursosDisponibles as $key => $val){
			$coursid= $val['Ciucourse']['id'];
			$coursname=$val[0]['name'];
			$opciones[$coursid]=$coursname;
		}
		$this->set(compact('opciones'));
	}
  
	function studentlistdata($cursoId = null){     
         $cursoSelect=$this->CiucourseCiustudent->Ciucourse->find('first',
                      array(
                      'fields'=>array('concat(Ciuuser.last_names," ",Ciuuser.first_names) as teacherName ,concat(Ciusubject.name," - ",Ciuroom.name," - ",Ciutimetable.name) as name'),
                      'conditions'=>array('Ciucourse.id'=>$cursoId)
                           )
                      );
         
         $sql= "select ciuuser_S.document, concat(ciuuser_S.last_names,' ',ciuuser_S.first_names) studentName,
                       ciucourse_ciustudent.score1,ciucourse_ciustudent.score2,ciucourse_ciustudent.score3,
                       ciucourse_ciustudent.score4,ciucourse_ciustudent.total_score
                  from ciuusers ciuuser_S
                  join ciucourses ciucourse
                  join ciucourse_ciustudents ciucourse_ciustudent on (ciucourse_ciustudent.ciuuser_id=ciuuser_S.id 
                                                                  and ciucourse_ciustudent.ciucourse_id=ciucourse.id)
                 where ciucourse.ciuacademicperiod_id in ( select id from ciuacademicperiods where isactive=1)
                   and ciucourse_ciustudent.ciucurspersstate_id = 3
                   and ciucourse.id = " .$cursoId.
              " order by 2";
        
       $listado= $this->Ciuuser->query($sql);   
       $this->set("cursoSelect", $cursoSelect['0']['name']);
       $this->set("teacherName", $cursoSelect['0']['teacherName']);
       $this->set("listado", $listado);
	}
  
	function studentlistpdf($cursoId = null) {     
        Configure::write('debug',0); // Otherwise we cannot use this method while developing 
        $this->studentlistdata($cursoId);
        $this->layout = 'pdf'; //this will use the pdf.ctp layout 
        $this->render(); 
	}
	
	function resendActivationMail(){
		$this->layout="login";
		$msg="";
		if (!empty($this->data)) {
		    $datosAspirante = $this->Ciuuser->findByEmail($this->data['Ciuuser']['email']);
			if($datosAspirante !=null){
				$this->sendmail(  $datosAspirante );
				$msg="Datos enviados al correo ". $datosAspirante['Ciuuser']['email'];
			}else{
				$msg="Correo electronico no encontrado. Favor verificar.";
			}
		}
		$this->set("msg",$msg);
	}	
}